package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.LinkedList;

import org.apache.tomcat.util.buf.UDecoder;

import util.JDBCUtil;

import domain.Comment;
import ex.DatabaseException;

public class CommentDAO {
	private Connection conn;
	private PreparedStatement stmt;
	private ResultSet rs;

	private static final String SELECT_COMMENT_BY_UIDR = "select * from comments where uidR= ? ";
	private static final String SELECT_COMMENT_BY_MID = "select * from comments where mid= ? ";
	private static final String UPDATE_COMMENT = "update comments set comment = ? where cid = ?";
	private static final String CREATE_COMMENT_ON_USER = "insert into comments  (uidS, uidR,  comment,flag) values (?,?,?,?) ";
	private static final String CREATE_COMMENT_ON_MOVIE = "insert into comments  (uidS, mid,  comment,flag) values (?,?,?,?) ";
	private static final String INCREMENT_FLAG = "update comments set flag = flag + 1 where cid = ?";
	private static final String DELETE_COMMENT = "delete comment where cid = ?";

	// Selection of the Comments using the User ID in the Comment table
	public LinkedList<Comment> selectCommentsByUidR(long uidR)
			throws DatabaseException {

		long uidS = -1;
		long mid = -1;
		long cid = -1;
		String cmnt = null;
		Timestamp timestamp = null;
		long flag = -1;
		LinkedList<Comment> comments = new LinkedList<Comment>();
		// get the columns of Comment table according to the user id of the
		// receiver
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(SELECT_COMMENT_BY_UIDR);
			stmt.setLong(1, uidR);
			rs = stmt.executeQuery();
			while (rs.next()) {
				uidS = rs.getLong("uidS");
				mid = rs.getLong("mid");
				cmnt = rs.getString("comment");
				cid = rs.getLong("cid");
				timestamp = rs.getTimestamp("timestamp");
				flag = rs.getLong("flag");
				UserDAO udao = new UserDAO();
				comments.add(new Comment(udao.selectUserByUid(uidS), uidR, mid,
						cmnt, timestamp, flag, cid));
			}
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} catch (SQLException e) {
			throw new DatabaseException("Problem with retrieving comments! "
					+ e.getMessage());
		}

		return comments;
	}

	public LinkedList<Comment> selectCommentsByMid(long mid)
			throws DatabaseException {

		long uidS = -1;
		long uidR = -1;
		long cid = -1;
		String cmnt = null;
		Timestamp timestamp = null;
		long flag = -1;
		LinkedList<Comment> comments = new LinkedList<Comment>();
		// get the columns of Comment table according to the movie id
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(SELECT_COMMENT_BY_MID);
			stmt.setLong(1, mid);
			rs = stmt.executeQuery();
			while (rs.next()) {
				uidS = rs.getLong("uidS");
				uidR = rs.getLong("uidR");
				cid = rs.getLong("cid");
				cmnt = rs.getString("comment");
				timestamp = rs.getTimestamp("timestamp");
				flag = rs.getLong("flag");
				UserDAO udao = new UserDAO();
				comments.add(new Comment(udao.selectUserByUid(uidS), uidR, mid,
						cmnt, timestamp, flag, cid));
			}
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} catch (SQLException e) {
			throw new DatabaseException("Problem with retrieving comments! "
					+ e.getMessage());
		}

		return comments;
	}

	// Insertion of a new Comment to the database
	public void insertCommentOnUser(Comment cmt) throws DatabaseException {

		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(CREATE_COMMENT_ON_USER);
			stmt.setLong(1, cmt.getUserS().getUid());
			stmt.setLong(2, cmt.getUidR());
			stmt.setString(3, cmt.getComment());
			stmt.setLong(4, cmt.getFlag());
			stmt.execute();
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} catch (SQLException e) {
			throw new DatabaseException("Problem with inserting comment! "
					+ e.getMessage());
		}

	}

	public void insertCommentOnMovie(Comment cmt) throws DatabaseException {

		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(CREATE_COMMENT_ON_MOVIE);
			stmt.setLong(1, cmt.getUserS().getUid());
			stmt.setLong(2, cmt.getMid());
			stmt.setString(3, cmt.getComment());
			stmt.setLong(4, cmt.getFlag());
			stmt.execute();
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} catch (SQLException e) {
			throw new DatabaseException("Problem with inserting comment! "
					+ e.getMessage());
		}

	}

	// Increasing the comment flag
	public void incrementCommentFlag(Comment cmt) throws DatabaseException {
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(INCREMENT_FLAG);
			stmt.setLong(1, cmt.getCid());
			stmt.execute();
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with incrementing comment flag! " + e.getMessage());
		}
	}

	// Removal of a Comment from the database
	public void deleteComment(Comment cmt) throws DatabaseException {
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(DELETE_COMMENT);
			stmt.setLong(1, cmt.getCid());
			stmt.execute();
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} catch (SQLException e) {
			throw new DatabaseException("Problem with deleting comment! "
					+ e.getMessage());
		}
	}

	// Editing the Comment fields
	public void updateComment(Comment cmt) throws DatabaseException {
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(UPDATE_COMMENT);
			stmt.setString(1, cmt.getComment());
			stmt.setLong(2, cmt.getCid());
			stmt.execute();
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} catch (SQLException e) {
			throw new DatabaseException("Problem with editing comment! "
					+ e.getMessage());
		}
	}

}
